package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.ProfTitle;

import lombok.extern.slf4j.Slf4j;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

@Slf4j
public final class ProfTitleDao {
    private static ProfTitleDao profTitleDao = new ProfTitleDao();

    private ProfTitleDao() {
    }

    public static ProfTitleDao getInstance() {
        if (profTitleDao == null) {
            profTitleDao = new ProfTitleDao();
        }
        return profTitleDao;
    }

    public static Collection<ProfTitle> findAll(Connection connection, List conditions_json, Pagination pagination) throws SQLException {
        Collection<ProfTitle> desiredProfTitles = new HashSet<ProfTitle>();
        int totalNum = CommonDao.count(connection, "ProfTitle");
        StringBuilder sql_select = new StringBuilder("SELECT*FROM ProfTitle");
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum));
        }
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {

            //根据id查找结果创建ProfTitle对象
            ProfTitle profTitle = new ProfTitle(
                    resultSet_select.getInt("id"),
                    resultSet_select.getString("description"),
                    resultSet_select.getString("no"),
                    resultSet_select.getString("remarks"));
            desiredProfTitles.add(profTitle);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        log.debug("" + desiredProfTitles);
        return desiredProfTitles;
    }


    //根据id，查找学院
    public ProfTitle find(Integer id, Connection connection) throws SQLException {

        String deleteProfTitle_sql = "select * from ProfTitle where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteProfTitle_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建profTitle对象
        ProfTitle profTitle = new ProfTitle(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(pstmt, connection);
        return profTitle;
    }

    //修改
    public boolean update(ProfTitle profTitle, Connection connection) throws SQLException {

        String deleteProfTitle_sql = "" +
                "UPDATE ProfTitle SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1, profTitle.getNo());
        pstmt.setString(2, profTitle.getDescription());
        pstmt.setString(3, profTitle.getRemarks());
        pstmt.setInt(4, profTitle.getId());
        return pstmt.executeUpdate() > 0;
    }

    //添加学院
    public boolean add(ProfTitle profTitle, Connection connection) throws SQLException {

        String deleteProfTitle_sql = "" +
                "INSERT INTO profTitle(no,description,remarks) VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1, profTitle.getNo());
        pstmt.setString(2, profTitle.getDescription());
        pstmt.setString(3, profTitle.getRemarks());
        //添加学院 并判断是否添加成功
        return pstmt.executeUpdate() > 0;
    }

    //根据ProfTitle对象删除
    public boolean delete(ProfTitle profTitle, Connection connection) throws SQLException {

        String deleteProfTitle_sql = "delete from ProfTitle where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1, profTitle.getNo());
        //根据id删除学院 并判断是否删除成功
        return pstmt.executeUpdate() > 0;
    }

}
